﻿--
--
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'spZeusPackageAdd' AND type = 'P')
BEGIN
  DROP PROCEDURE spZeusPackageAdd
END
GO

-- HU - Rakomány felvétel
-- EN - Package insert
--
CREATE PROCEDURE spZeusPackageAdd
(@Lang NVARCHAR(10), @SupplyID INTEGER, @Code NVARCHAR(20), @EAN NVARCHAR(128), @Quantity DECIMAL(18,6), @SupplyItemID INTEGER)
 AS
BEGIN
  DECLARE
    @Rows INTEGER,
    @ID INTEGER


  DECLARE
    @ArticleCode NVARCHAR(20),
    @ArticleName NVARCHAR(256),
    @UnitCode NVARCHAR(10),
    @Expired NVARCHAR(10),
    @AI01 NVARCHAR(16)


  -- 
  --
  IF (@SupplyID IS NULL) OR (@SupplyID = 0) BEGIN

    SELECT @SupplyID = NULL
  END

  -- Product 
  --
  IF (ISNULL(@SupplyItemID, 0) != 0) BEGIN

    SELECT @ArticleCode = ArticleCode,
           @ArticleName = ArticleName,
           @UnitCode = UnitCode,
           @Expired = Expired
      FROM ZeusSupplyItem
     WHERE ID = @SupplyItemID
    
    SELECT @Rows = @@ROWCOUNT

    IF (@Rows != 1) BEGIN

      IF (@Lang = 'HU')
        RAISERROR (N'Nincs ilyen termék %d !', 16, 1, @SupplyItemID)

      IF (@Lang = 'EN')
        RAISERROR (N'There is no such article with this ID %d !', 16, 1, @SupplyItemID)

      IF (@Lang = 'DE')
        RAISERROR (N'Keine Produkte ID %d !', 16, 1, @SupplyItemID)

      GOTO __ERROR__
    END
  END


  -- Simple product code EAN8, EAN13, GTIN, UPC or something
  --
  IF (LEN(@EAN) <= 14) BEGIN

    SELECT @ArticleCode = Code,
           @ArticleName = Name,
           @UnitCode = Unit,
           @Expired = N''
      FROM ZeusArticle
     WHERE EAN = @EAN

    SELECT @Rows = @@ROWCOUNT
  
  END
  ELSE BEGIN

    -- Parse UCC/EAN-128
    --
    SELECT 
      @AI01 = CASE 
                WHEN (ISNULL(AI01, N'') != N'') THEN AI01
                WHEN (ISNULL(AI02, N'') != N'') THEN AI02
              ELSE NULL END,

      @Expired = CASE 
                   WHEN (ISNULL(AI15, N'') != N'') THEN N'20' + AI15
                   WHEN (ISNULL(AI17, N'') != N'') THEN N'20' + AI17
                 ELSE NULL END

      FROM dbo.fnEAN128(@EAN)

    SELECT @ArticleCode = Code,
           @ArticleName = Name,
           @UnitCode = Unit
      FROM ZeusArticle
     WHERE EAN = @AI01

    SELECT @Rows = @@ROWCOUNT
  
  END

  IF (@Rows != 1) BEGIN

    IF (@Lang = 'HU')
      RAISERROR (N'Nincs ilyen termék EAN %s !', 16, 1, @EAN)

    IF (@Lang = 'EN')
      RAISERROR (N'There is no such article with this EAN %s !', 16, 1, @EAN)

    IF (@Lang = 'DE')
      RAISERROR (N'Keine Produkte EAN-Code %s !', 16, 1, @EAN)

    GOTO __ERROR__
  END

  IF (ISNULL(@Quantity, 0) < 0) BEGIN

    IF (@Lang = 'HU')
      RAISERROR (N'Mennyiség nem lehet negatív !', 16, 1)

    IF (@Lang = 'EN')
      RAISERROR (N'The Quantity can not be negative !', 16, 1)

    IF (@Lang = 'DE')
      RAISERROR (N'Menge kann nicht negativ sein !', 16, 1)
          
    GOTO __ERROR__
  END

  -- 
  --
  BEGIN TRY
        
    INSERT INTO ZeusPackage
      (SupplyID, Code, ArticleCode, ArticleName, Expired, Quantity, UnitCode, Refill)
    VALUES
      (@SupplyID, @Code, @ArticleCode, @ArticleName, @Expired, @Quantity, @UnitCode, 'N')
     
    SELECT @Rows = @@ROWCOUNT, @ID = @@IDENTITY
  
  END TRY
  BEGIN CATCH

    IF (@Lang = 'HU')
      RAISERROR (N'Rakomány átvétel nem sikerült !', 16, 1)

    IF (@Lang = 'EN')
      RAISERROR ('Package receive did not go so well.', 16, 1)

    IF (@Lang = 'DE')
      RAISERROR ('Erhalt der Ware gescheitert !', 16, 1)

    GOTO __ERROR__
  END CATCH

    
-- Well done.
--
  RETURN(0)

-- We have a problem !
--
__ERROR__:
  RETURN(1)
END
GO
